package repositorio;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import face.DataModel;

import model.DiaDaSemana;
import model.Disciplina;
import model.Professor;
import model.Turma;

public class TurmaRepositorio {

	public boolean incluir(Turma entidade) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("INSERT INTO TURMA (CD_TURMA, " +
														 "SALA, " +
														 "DIA_SEMANA, " +
														 "MATRICULA_PROFESSOR, " +
														 "NOME_DISCIPLINA) " +
									  "VALUES (?, ?, ?, ?, ?)");
			ps.setString(1, entidade.getCdTurma());
			ps.setInt(2, entidade.getSala());
			ps.setInt(3, entidade.getDiaDaSemana().ordinal());
			ps.setInt(4, entidade.getProfessor().getNrMatricula());
			ps.setString(5, entidade.getDisciplina().getNome());
			
			return (ps.executeUpdate() > 0);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}

	public boolean atualizar(Turma entidade) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("UPDATE TURMA SET CD_TURMA = ?, " +
														 "SALA = ?, " +
														 "DIA_SEMANA = ?, " +
														 "MATRICULA_PROFESSOR = ?, " +
														 "NOME_DISCIPLINA = ?");
			ps.setString(1, entidade.getCdTurma());
			ps.setInt(2, entidade.getSala());
			ps.setInt(3, entidade.getDiaDaSemana().ordinal());
			ps.setInt(4, entidade.getProfessor().getNrMatricula());
			ps.setString(5, entidade.getDisciplina().getNome());
			return ps.executeUpdate() > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}

	public boolean remover(Turma entidade) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("DELETE FROM TURMA WHERE CD_TURMA = ?");
			ps.setString(1, entidade.getCdTurma());
			return ps.executeUpdate() > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}

	public List<Turma> buscarTodos() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Turma turma = null;
		List<Turma> list = new ArrayList<Turma>();
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT * FROM TURMA ORDER BY CD_TURMA");
			rs = ps.executeQuery();
			while (rs.next()) {
				turma = new Turma();
				turma.setCdTurma(rs.getString("CD_TURMA"));
				turma.setDiaDaSemana(DiaDaSemana.values()[rs.getInt("DIA_SEMANA")]);
				turma.setSala(rs.getInt("SALA"));
				turma.setDisciplina(getDisciplina(rs.getString("NOME_DISCIPLINA")));
				turma.setProfessor(getProfessor(rs.getInt("MATRICULA_PROFESSOR")));
				list.add(turma);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}
		return list;
	}

	public Turma buscarPorPk(Turma entidade) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Turma turma = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT * FROM TURMA WHERE CD_TURMA = ?");
			ps.setString(1, entidade.getCdTurma());
			rs = ps.executeQuery();
			if (rs.next()) {
				turma = new Turma();
				turma.setCdTurma(rs.getString("CD_TURMA"));
				turma.setDiaDaSemana(DiaDaSemana.values()[rs.getInt("DIA_SEMANA")]);
				turma.setSala(rs.getInt("SALA"));
				turma.setDisciplina(getDisciplina(rs.getString("NOME_DISCIPPLINA")));
				turma.setProfessor(getProfessor(rs.getInt("MATRICULA_PROFESSOR")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}
		return turma;
	}
	
	public List<Integer> buscarMatriculasDaTurma(Turma entidade) {
		List<Integer> matrAlunos = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT A.MATRICULA " +
									  "FROM MATRICULA M " +
									  "INNER JOIN ALUNO A ON A.MATRICULA = M.MATRICULA_ALUNO " +
									  "WHERE CD_TURMA = ?");
			ps.setString(1, entidade.getCdTurma());
			rs = ps.executeQuery();
			
			matrAlunos = new ArrayList<Integer>();
			
			while (rs.next()) {
				matrAlunos.add(rs.getInt(1));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			BDUtil.finalizeConnection(rs, ps, con);
		}
		return matrAlunos;
	}
	
	
	
	public DataModel buscarConceitos(Turma entidade) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("SELECT A.MATRICULA," +
											 "A.NOME, " +
										     "M.PARCIAL_1, " +
										     "M.PARCIAL_2, " +
										     "M.PARCIAL_3, " +
										     "M.PARCIAL_4, " +
										     "M.FINAL " +
									  "FROM MATRICULA M " +
									  "INNER JOIN ALUNO A ON A.MATRICULA = M.MATRICULA_ALUNO " +
									  "WHERE CD_TURMA = ?");
			
			ps.setString(1, entidade.getCdTurma());
			rs = ps.executeQuery();
			return new DataModel(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs = null;
			BDUtil.finalizeConnection(rs, ps, con);
		}
		return null;
	}
	
	
	public boolean alterarConceitos(int matriculaAluno, String cdTurma, 
			String parcial1, String parcial2, String parcial3, String parcial4, String cfinal) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			ps = con.prepareStatement("UPDATE MATRICULA SET " +
												"PARCIAL_1=?, " +
												"PARCIAL_2=?, " +
												"PARCIAL_3=?, " +
												"PARCIAL_4=?, " +
												"FINAL=? " +
									  "WHERE MATRICULA_ALUNO =?" +
									    "AND CD_TURMA =?");
			ps.setString(1, parcial1.toUpperCase());
			ps.setString(2, parcial2.toUpperCase());
			ps.setString(3, parcial3.toUpperCase());
			ps.setString(4, parcial4.toUpperCase());
			ps.setString(5, cfinal.toUpperCase());
			ps.setInt(6, matriculaAluno);
			ps.setString(7, cdTurma);
			
			return (ps.executeUpdate() > 0);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			BDUtil.finalizeConnection(ps, con);
		}
	}
	
	
	
	
	
	public Disciplina getDisciplina(String pk){
		
		DisciplinaRepositorio dao = new DisciplinaRepositorio();
		Disciplina d = new Disciplina();
		d.setNome(pk);
		return dao.buscarPorPk(d);
		
	}
	
	public Professor getProfessor(int pk){
		
		ProfessorRepositorio dao = new ProfessorRepositorio();
		Professor p = new Professor();
		p.setNrMatricula(pk);
		return dao.buscarPorPk(p);
		
	}
	
	
	
	
	

}
